set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=400;
set hive.exec.max.dynamic.partitions.pernode=400;

insert overwrite table jms_dwm.dwm_analysis_target_network_sum_day_dt
select
     base.stat_date
    ,dim.network_type
    ,dim.code as network_code
    ,dim.name as network_name
    ,dim.fran_code
    ,dim.fran_name
    ,dim.agent_code
    ,dim.agent_name
    ,dim.virt_code
    ,dim.virt_name
    ,base.kpi_code
    ,base.kpi_name
    ,base.kpi_type
    ,base.kpi_fz
    ,base.kpi_fm
    ,base.kpi_values
    ,base.kpi_param
    ,agent.principal_name  as agent_principal_name
    ,fran.principal        as franchisee_principal_name
    ,dim.principal         as principal_name
    ,ind.alias as kpi_alias
    ,base.dt as dt
from (
    select *
    from jms_dwm.dwm_overall_target_network_sum_mid_dt
    where dt between date_sub('{{ execution_date | cst_ds }}',30) and '{{ execution_date | cst_ds }}'
) base
left join jms_dim.dim_network_whole_massage dim
   on base.network_code = dim.code
left join (
    select
         agent_code
        ,agent_name
        ,virt_code
        ,virt_name
        ,split(principal,'/')[1]  as principal_name
    from jms_dim.dim_network_whole_massage
    where network_type = 2   ---代理区负责人
    group by agent_code
            ,agent_name
            ,virt_code
            ,virt_name
            ,split(principal,'/')[1]
) agent on dim.agent_code = agent.agent_code
left join (
    select code,name,principal
    from jms_dim.dim_network_whole_massage
    where network_type = '3'
    group by code,name,principal
) fran on dim.fran_code =fran.code
left join jms_dwd.dwd_indicator_onlinezation ind
    on base.kpi_code = ind.code
where dim.agent_name not in ('总部市场部','西藏代理区')
distribute by dt,pmod(hash(rand()),1)
;
